package Sql;

import Table.HeadUnit;
import Table.TableInfoCenter;
import Table.getAllInfo;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;
/**
* 处理数据库连接和访问
* @version 1.01
*/
public class Mysql  {

	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement prepstmt = null;
	
	//这是个全局类,里面放置数据库参数,如数据库主机 访问用户名 密码等
	
	/**
	* 构造数据库连接和访问类
	*/
	public Mysql() throws Exception {
		Class.forName(Config.DBDRIVER);
		conn = DriverManager.getConnection(Config.DBURL, Config.DBUSER, Config.DBPASS);
		stmt = conn.createStatement();
	}
	public Mysql(String sql) throws Exception {
		Class.forName(Config.DBDRIVER);
		conn = DriverManager.getConnection(Config.DBURL);
		this.prepareStatement(sql);
	}
	
	/**
	* 返回连接
	* @ Connection 连接
	*/
	public Connection getConnection() {
	 return conn;
	}


	/**
	* 执行SQL语句返回字段集
	* @param sql SQL语句
	* @ ResultSet 字段集
	*/
	public ResultSet executeQuery(String sql) throws SQLException {
	 if(stmt != null) {
	 		return stmt.executeQuery(sql);
		}
	 else return null;
	}
	
	public ResultSet executeQuery() throws SQLException {
	 	if(prepstmt != null) {
		 	return prepstmt.executeQuery();
		}
	 	else return null;
	}
	
	/**
	* 执行SQL语句
	* @param sql SQL语句
	*/
	public void executeUpdate(String sql) throws SQLException {
	 if(stmt != null) 
		stmt.executeUpdate(sql);
	}
	public void executeUpdate() throws SQLException {
	 	if(prepstmt != null)
	 		prepstmt.executeUpdate();
	}
	
	/**
	* 关闭连接
	*/
	public void close() throws Exception {
	 if(stmt != null) {
			stmt.close();
			stmt = null;
		}
	 if(prepstmt != null) {
			prepstmt.close();
			prepstmt = null;
		}
		conn.close();
		conn = null;
	}
	
	
	
	/****************************************************************
	 *
	 ******************* 为专门的操作而开发的函数    *******************
	 *
	*****************************************************************/
	
	
	
	
	/**   
	 * <p>检查用户
	 * <p>并返回用户级别
	 * <p>0 -> 用户不存在
	 * <p>1 -> 人员管理员
	 * <p>2 -> 仓库管理员
	 * <p>3 -> 进货人员
	 * <p>4 -> 出货人员
	 * <p>5 -> 无权人员
	 * 
	 */
	public int checkUser(String uid,String password) throws SQLException{
		ResultSet rs = this.executeQuery("select * from user where uid = '" + uid + "' and password = '" + password + "'");
		if(rs.next()){
			return rs.getInt("level");
		}else return 0;
	}
	
	public void updateLastLoginTime(String uid) throws SQLException {
            this.executeUpdate("update user set lastlogin = now() where uid = '" + uid + "'");
        }
	

	


	/**
	 *
	 * @param uid
	 * @return Mysql_User
	 * @throws SQLException
	 *
	 * <p>返回该用户(uid)的所有相关信息（如果你得到的lastlogin为空的话表示这是该用户的第一次登录）
	 *
	 */

	public User getUserInfo(String uid) throws SQLException{

		User user = new User();
		ResultSet rs = this.executeQuery("select * from view_user where uid = '" + uid + "'");

		if(rs.next()){
			user.setUid(rs.getString("uid"));
			user.setUname( rs.getString("uname"));
			user.setUsex(rs.getString("usex"));
			user.setPassword( rs.getString("password"));
			user.setLevel( rs.getInt("level"));
			user.setLevelname( rs.getString("levelname"));
			user.setLastlogin( rs.getDate("lastlogin"));
			user.setAddress( rs.getString("address"));
			user.setEmail( rs.getString("email"));
			user.setTel( rs.getString("tel"));
			user.setOthers( rs.getString("others"));
		}

		return user;
	}


	/**
	 * 这是个返回对象数组的 获得某个用户信息函数
	 * @param uid
	 * @return
	 * @throws SQLException
	 */

	public Object[] getUserInfoV2(String uid) throws SQLException{

		ResultSet rs = this.executeQuery("select * from view_user where uid = '" + uid + "'");

		if(rs.next()){
			Object[] obj = {
					rs.getString("uid"),
					rs.getString("uname"),
					rs.getString("usex"),
					rs.getString("password"),
					rs.getInt("level"),
					rs.getString("levelname"),
					rs.getDate("lastlogin"),
					rs.getString("address"),
					rs.getString("email"),
					rs.getString("tel"),
					rs.getString("others")
			} ;
			return obj;
		}else return null;

	}

	/**
	 * <p>得到所有用户信息
	 * <p>以一个二维对象数组返回
	 * @return
	 * @throws SQLException
	 */

	public Object[][] getAllUserV2() throws SQLException{
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		ResultSet rs = this.executeQuery("select * from view_user ");

		while(rs.next()){
			Object[] obj = {
					rs.getString("uid"),
					rs.getString("uname"),
					rs.getString("usex"),
					rs.getString("password"),
					rs.getInt("level"),
					rs.getString("levelname"),
					rs.getDate("lastlogin"),
					rs.getString("address"),
					rs.getString("email"),
					rs.getString("tel"),
					rs.getString("others")
			} ;
			al.add(obj);
		}

		Object[][] obj = new Object[al.size()][11];
		for(int i = 0; i < al.size(); i ++){
			obj[i] = al.get(i);
		}

		return obj;
	}





	/**
	 *
	 * @param user
	 * @throws SQLException
	 *
	 * <p>只要你传入一个User参数，该函数可以直接修改数据库中的数据
	 *
	 * <p>如果传了一个原来没有的uid，则会插入一个新的用户
	 *
	 * <p>修改 levelname 不会改变数据库中级别，要修改 level
	 */

	public boolean updateUserInfo(User user) throws SQLException{

		if((this.executeQuery("select * from user where uid = '"+user.getUid()+"'")).next()){
			  // 修改 user 表
			this.executeUpdate("update user set uname = '"+user.getUname()+"',usex = '"+user.getUsex()+"', password = '"+user.getPassword()+"',level = "+user.getLevel()+" where uid = '"+user.getUid()+"'" );

			  // 修改 userinfo 表
			if( (this.executeQuery("select * from userinfo where uid = '" + user.getUid() + "'")).next() ){
       
				this.executeUpdate("update userinfo set address = '"+user.getAddress()+"' , email ='"+user.getEmail()+"', tel = '"+user.getTel()+"', others = '"+user.getOthers()+"' where uid ='"+user.getUid()+"'");

			}else{
                         
				this.executeUpdate("insert into userinfo values('"+user.getUid()+"','"+user.getAddress()+"','"+user.getEmail()+"','"+user.getTel()+"','"+user.getOthers()+"')");
			}
                   return true ;
		// 如果以前没有该uid的相关记录，则插入新的
		}else{
                     return false ;
//			this.executeUpdate("insert into user values('"+ user.getUid() +"','"+ user.getUname() +"','"+ user.getPassword() +"','"+ user.getLevel() +"','"+ user.getLastlogin() +"')");
//
//			this.executeUpdate("insert into userinfo values('"+user.getUid()+"','"+user.getAddress()+"','"+user.getEmail()+"','"+user.getTel()+"','"+user.getOthers()+"')");
		}

	}


	










        
	
	
	
	/*****************************************************
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 下面是货物Goods相关的一些函数
	 * 
	 * 
	 * 
	 */
	
	
	/**
	 * 
	 * @param String gid
	 * @return Mysql_Goods
	 * @throws SQLException
	 * 
	 */
	
	public Goods getGoodsInfo(String gid) throws SQLException{
		
		Goods good = new Goods();
		
		ResultSet rs = this.executeQuery("select * from view_goods where gid = '"+gid+"'");
		
		if(rs.next()){
			
			good.setGid(rs.getString("gid"));
			good.setGname(rs.getString("gname"));
			good.setUnit(rs.getString("unit"));
			good.setNum(rs.getInt("num"));
			good.setCid(rs.getString("cid"));
			good.setCname(rs.getString("cname"));
			good.setMinnum(rs.getInt("minnum"));
			good.setMaxnum(rs.getInt("maxnum"));
			good.setMinnum(rs.getInt("minnum"));
			good.setMaxnum(rs.getInt("maxnum"));
			good.setPrice(rs.getFloat("price"));
			good.setCreatetime(rs.getDate("createtime"));
			good.setSavetime(rs.getString("savetime"));
			good.setOthers(rs.getString("others"));
		}
			
		return good;
		
	}
	/**
	 * <p>如果给该函数提供了一个数据库中没有的 gid ，则会插入一个新记录
	 * 
	 * <p> 这里也不能修改 cname ，只能通过修改 cid 来改变 goods 种类
	 * 
	 * @param Goods
	 * @throws SQLException
	 */
	
	public void updateGoodsInfo(Goods good) throws SQLException{
		
		
		// 修改 goods 表
		if( (this.executeQuery("select * from goods where gid = '" + good.getGid() + "'")).next() ){
			
			this.executeUpdate("update goods set gname = '"+good.getGname()+"', unit ='"+good.getUnit()+"', num = '"+
					good.getNum()+"', cid = '"+good.getCid()+"',minnum = '"+good.getMinnum()+"', maxnum = '"+
					good.getMaxnum()+"' where gid ='"+good.getGid()+"'");
			
			//修改 goodsinfo 表，这要分两种情况： goodsinfo 有关于gid的相关记录，  或者没有。
			if( (this.executeQuery("select * from goodsinfo where gid = '" + good.getGid() + "'")).next()  ){
				this.executeUpdate("update goodsinfo set price = '"+ good.getPrice() +"', createtime = '"+ good.getCreatetime() +"',savetime ='"+ good.getSavetime() +"', others = '' where gid = '"+ good.getGid() +"'");
			}else{
				this.executeUpdate("insert into goodsinfo(gid,price,createtime,savetime,others) values('"+ good.getGid() +"','"+ 
						good.getPrice()+"','"+ good.getCreatetime() +"','"+ good.getSavetime() +"','"+ good.getOthers() +"')");
			}
			
		// 如果没有相关的 gid 时，则插入一个新记录
		}else{
			
			// 插入 goods 表
			this.executeUpdate("insert into goods(gid,gname,unit,num,cid,minnum,maxnum) " +
					"values('"+good.getGid()+"','"+good.getGname()+"','"+good.getUnit()+"','"+good.getNum()+"','"+
					good.getCid()+"','"+good.getMinnum()+"','"+good.getMaxnum()+"')");
			
			// 插入 goodsinfo 表
			this.executeUpdate("insert into goodsinfo(gid,price,createtime,savetime,others) values('"+ good.getGid() +"','"+ 
					good.getPrice()+"','"+ good.getCreatetime() +"','"+ good.getSavetime() +"','"+ good.getOthers() +"')");
		
		} 
		
		
	}
	
	
	
	
	
	
	/**  不常用函数  ***/
	
	/**
	* PreparedStatement
	* @ sql 预设SQL语句
	*/ 
	public void prepareStatement(String sql) throws SQLException {
		prepstmt = conn.prepareStatement(sql);
	}
	/**
	* 设置对应值
	* @param index 参数索引
	* @param value 对应值
	*/
	public void String(int index,String value) throws SQLException {
		prepstmt.setString(index,value);
	}
	public void Int(int index,int value) throws SQLException {
		prepstmt.setInt(index,value);
	}
	public void Boolean(int index,boolean value) throws SQLException {
		prepstmt.setBoolean(index,value);
	}
	public void Date(int index,Date value) throws SQLException {
		prepstmt.setDate(index,value);
	}
	public void Long(int index,long value) throws SQLException {
		prepstmt.setLong(index,value);
	}
	public void Float(int index,float value) throws SQLException {
		prepstmt.setFloat(index,value);
	}
	//File file =  File(\"test/data.txt\");
	// fileLength = file.length;
	//InputStream fin =  java.io.FileInputStream(file);
	//mysql.BinaryStream(5,fin,fileLength);
	public void BinaryStream(int index,InputStream in,int length) throws SQLException {
		prepstmt.setBinaryStream(index,in,length);
	} 
	
	public void clearParameters() throws SQLException{
		prepstmt.clearParameters();
	}
	
	/**
	* 返回预设状态
	*/
	public PreparedStatement getPreparedStatement() {
	 return prepstmt;
	}
	
	/**
	* 返回状态
	* @ Statement 状态
	*/
	public Statement getStatement() {
	 return stmt;
	}

}